# 作业先完成，再完美
# 问：简单使用Mysql约束, 并举例说明使用场景.
# 答：
# create table student(
# id INT PRIMARY KEY auto_increment,
# name VARCHAR(191) NOT NULL UNIQUE,
# class VARCHAR(191) NOT NULL,
# isFinish_hw INT NOT NULL
# )
# 使用场景：痴海训练营第二期学员表，id是一个表中的唯一标识，主键约束。一个学生表，他的名字不能为空且唯一，班级和作业完成与否不能为空。

# 问：练习使用分组查询.
#插入一些数据：第七周作业完成数据
# INSERT INTO student(name, class, isFinish_hw)
# VALUES('7', '1班', 0),('Bao', '1班', 0),('YJ', '1班', 0),
# ('一只迷路的熊', '1班', 1),('兰亭生葳蕤', '1班', 0),('嘘嘘', '1班', 1),
# ('心洛', '1班', 0),('林海清', '1班', 0),('浪漫野心家', '1班', 0),
# ('王婉怡', '1班', 0),('🇼 🇾 🇼 🇭', '1班', 0), ('abyss', '2班', 0),
# ('chaos', '2班', 1), ('spiderchen', '2班', 0), ('Super_Coding', '2班', 1),
# ('Wong', '2班', 1), ('俊焺', '2班', 0), ('小屋', '2班', 1), ('小飞侠', '2班', 1),
# ('晓宁', '2班', 0), ('曦', '2班', 1), ('芊小芊', '2班', 0), ('陈生', '2班', 1),
# ('Allisan凯', '3班', 0),('DDDDian', '3班', 0),('Flowey', '3班', 0),
# ('余余余', '3班', 0),('哲', '3班', 0),('培嘉', '3班', 1),('小丸子', '3班', 0),
# ('李泊', '3班', 1),('茗茗是只假鸽子', '3班', 1),('逆光年', '3班', 0),
# ('陶冶', '3班', 0),('Drift', '4班', 0),('LiPing', '4班', 1),
# ('冰鹰', '4班', 0),('周岱', '4班', 0),('安逸', '4班', 1),('德里克', '4班', 0),
# ('无形之刃、最为致命', '4班', 1),('武明', '4班', 1),('每日充电', '4班', 0),
# ('胡志师', '4班', 0),('A-梦初', '5班', 0),('Bruce', '5班', 0),
# ('Carmen', '5班', 1),('H', '5班', 0),('Leo_宗远', '5班', 0),
# ('云', '5班', 1),('刘强', '5班', 0),('嘿嘿', '5班', 0),('年轻人不讲吾德', '5班', 0),
# ('张兴雨', '5班', 1),('拆拆拆', '5班', 0),('陈祎祺', '5班', 0),('朱先森', '5班', 0)

# 分组查询
# SELECT class as 班级,
# COUNT(*) as 人数,
# SUM(isFinish_hw) as 完成人数,
# FORMAT(SUM(isFinish_hw)/COUNT(*), 2) as 作业完成率,
# MAX(isFinish_hw) as 最大值,
# MIN(isFinish_hw) as 最小值
# FROM student GROUP BY class